<?php
//Initiate Authenticate
SecurityRedirect ();

//initiate Vars
define("PRIMARY_KEY","id");

$FileName       = "ais_tools_tariff_packet.php";
$FileTemplate   = "ais_tools_tariff_packet_form.html";
$SubHeaderTitle = "Tariff Packet";

$TableName      = "$DBApp.tbltariffheader";
$TableDetail    = "$DBApp.tbltariffdetail";
$TableDepo      = "$DBApp.tbldepo";
$TableReceipt   = "$DBApp.tblreceipt";

$HTMLFormName   = "lessor_agreement";
$HTMLFormNameCharges   = "charges";
$HTMLFormNameContainer   = "container";

$FormAction    = $FileName . "?" . AddParam(GetQueryString("QueryString", ""),
                                            "form","$HTMLFormName");
$FormEncType   = "application/x-www-form-urlencoded";


//Initiate Database Table For Insert & Update;
$FormFields = array("id","tbldepo_id","name","container_size",
                    "date_start","date_end","created","modified",
                    "created_by","modified_by");
buildsqlfield ("$TableName", $FormFields);

//Initiate Database Table For Insert & Update;
$FormFieldsDetail = array("id","tbltariffheader_id","tblreceipt_id","rate","coa",
                          "cont_code_code","cont_code_desc","cont_type_desc","created","modified",
                          "created_by","modified_by");
buildsqlfield ("$TableDetail", $FormFieldsDetail);

//Leave Here Untouch
$FormCName     = BuildHTMLField($FormFields,"name");
$FormCValue    = BuildHTMLField($FormFields,"value");
$FormDetailName = BuildHTMLField($FormFieldsDetail,"name");
$FormDetailValue= BuildHTMLField($FormFieldsDetail,"value");


//************ FORM ACTION **************
if (GetParam("form","")==$HTMLFormName) {
    if (GetParam("Action","")=="Add") {
        FormInsert();
    }
    if (GetParam("Action","")=="Update") {
        $FormMode="Edit";
        FormUpdate();
    }
    if (GetParam("Action","")=="Delete") {
        $FormMode="Edit";
        FormDelete();
    }
    if (GetParam("Action","")=="Cancel") {
        $FormMode="Edit";
        FormCancel();
    }
}

//Field Verification
$Verification  = "Enable";
$SessionVar    = md5(microtime());
$SessionVar    = "Stuck";
$VerifyFields  = array("tbldepo_id"=>"Depo Id",
                       "date_start"=>"Start Date",
                       "date_end"=>"End Date");
VerifyHTML ($HTMLFormName,$VerifyFields,$SessionVar);


//************ SET FORM MODE ***************
// PENAMBAHAN SKRIP DISINI \\
if (GetParam(PRIMARY_KEY,"")) {
        $FormMode = "Edit";
} else {
        $FormMode = "Add";
}

Init($SessionVar,CreateVerifyLoader ($FileName,"iLoader",PRIMARY_KEY,"$HTMLFormName.".PRIMARY_KEY, $FormMode));

$InitFunction = "isExist".PRIMARY_KEY."()";

//******* DATABASE FORM QUERY ***********
$SQL = "SELECT hd.id as id,hd.tbldepo_id as tbldepo_id,hd.name as name,hd.date_start as start_date,hd.date_end as end_date,
        hd.created as created,hd.modified as modified,hd.created_by as created_by,hd.modified_by as modified_by,hd.container_size as container_size
        FROM $TableName hd";
if (GetParam("tbldepo_id","") && $FormMode="Edit") {
    $SQLForm = $SQL." WHERE hd.tbldepo_id='".trim(GetParam("tbldepo_id",""))."'";
    $sqlDataHeader  = "SELECT hd.id as id,hd.tbldepo_id as tbldepo_id,hd.name as name,hd.date_start as start_date,
                       hd.date_end as end_date, hd.created as created,hd.modified as modified,hd.created_by as created_by,
                       hd.modified_by as modified_by,hd.container_size as container_size, 
                       dp.name as depoName
                       FROM $TableName hd LEFT JOIN $TableDepo dp ON hd.tbldepo_id=dp.id
                       WHERE hd.id='".trim(GetParam("id",""))."'";

    $sqlDataCharges = "SELECT det.id,det.tbltariffheader_id,det.tblreceipt_id,rec.receiptname,
                       det.rate,det.coa,det.created,det.modified,det.created_by,det.modified_by
                       FROM $TableDetail det LEFT JOIN $TableReceipt rec ON det.tblreceipt_id=rec.kdreceipt 
                       WHERE det.tbltariffheader_id='".trim(GetParam(PRIMARY_KEY,""))."';";
    
    $Queryid = mysql_query("SELECT dp.name FROM $TableDepo dp WHERE dp.id='".trim(GetParam("tbldepo_id",""))."'");
    $fetchingOid = mysql_fetch_assoc($Queryid);
    $depoName = $fetchingOid[name];
    $listTariff = 1;
}

//******* SHOW FORM CONTENT *********
$TBS = new clsTinyButStrong ;
$TBS->LoadTemplate($MOD_TEMPLATE_DIR."/".$FileTemplate);

$SQLDepo = "SELECT * FROM $TableDepo";
$TBS->MergeBlock("depotMaster",$DBMysql,$SQLDepo);

$SQLSize = array('20'=>'20','40'=>'40','45'=>'45');
$TBS->MergeBlock("containerSize",$SQLSize);

$SQLItem = "SELECT kdreceipt,receiptname FROM $TableReceipt";
$TBS->MergeBlock("receipt",$DBMysql,$SQLItem);

$dataCr = mysql_query($SQLItem);
$stringItem = "";
$stringValue = "";
while($rowC = mysql_fetch_assoc($dataCr)) {
    if($stringItem!="" && $stringValue!=""){
        $stringItem .= ", '". $rowC['kdreceipt']."'";
        $stringValue .= ", '". $rowC['receiptname']."'";
    } else {
        $stringItem .= "'".$rowC['kdreceipt']."'";
        $stringValue .= "'".$rowC['receiptname']."'";
    }
}

if (GetParam("tbldepo_id","")) {
    $TBS->MergeBlock("blv","mysql",$SQLForm);
    $TBS->MergeBlock("dem","mysql",$sqlDataCharges);
    $TBS->MergeBlock("atas","mysql",$SQLForm);
    $TBS->MergeBlock("hd","mysql",$sqlDataHeader);
} else{
    $TBS->MergeBlock("blv",$FormCValue);
    $TBS->MergeBlock("chrg",$FormDetailValue);
    $TBS->MergeBlock("dem",$FormDetailValue);
}
//HTML Name
$TBS->MergeBlock("bln",$FormCName);

$TBS->Show();

//*************** ACTION FUNCTION *******************/
function FormInsert() {
    
    global $DBCoins,$DBGeneral,$DBConnection,$FileName,$TableName,$TableLessor,$TableAgentAgrCharges,$TableAgrTy;
    global $TableLessorContainer,$TableLessorCharges,$TableAgent;

    $dataHeader = array();
    If ( isset( $_POST['start_date'] ) ) {
        $dataHead['start_date'] = formatDateToMysql($_POST['start_date']);
    }

    If ( isset( $_POST['end_date'] ) ) {
        $dataHead['end_date'] = formatDateToMysql($_POST['end_date']);
    }

    $sqlInsert = "INSERT INTO $TableName
                  (tbldepo_id,container_size,name,date_start,date_end,
                  created,created_by)
                  VALUES
                  ('".$_POST['depoName']."','".$_POST['contSize']."','".$_POST['tariffName']."',
                   '".$dataHead['start_date']."','".$dataHead['end_date']."',
                   now(),'".GetStaffName()."'); ";


    $DBConnection->dbc->query($sqlInsert);

	/** Insert Demurage **/
	$numDem = $_POST['numitems3'];
    $detailDem = array();
    for($x=1;$x<=$numDem;$x++){
        $detailDem[$x]['detail_item'] = $_POST['detail_item_'.$x];
        $detailDem[$x]['rate'] = $_POST['rate_'.$x];
        $detailDem[$x]['coa'] = $_POST['coa_'.$x];
    }
    
    $Queryid = mysql_query("SELECT id FROM $TableName ORDER BY id DESC limit 1");
    $fetchingOid = mysql_fetch_assoc($Queryid);
    
	foreach($detailDem as $key => $val) {
		$sqlDem = "INSERT INTO tbltariffdetail (tbltariffheader_id,tblreceipt_id,rate,coa,created,created_by)
                   VALUES ('$fetchingOid[id]','$val[detail_item]','$val[rate]','$val[coa]',NOW(),'".GetStaffName()."');";
		$DBConnection->dbc->query($sqlDem);
	}

    //echo $sqlDem;
    //exit;  

    header("Location: $FileName");
    exit;
}

function FormUpdate() {

    global $DBCoins,$DBGeneral,$DBConnection,$FileName,$TableName,$TableLessor,$TableAgentAgrCharges,$TableAgrTy;
    global $TableLessorContainer,$TableLessorCharges,$TableAgent;

    $dataHeader = array();
    If ( isset( $_POST['agent_agr_start'] ) ) {
        $dataHead['agent_agr_start'] = formatDateToMysql($_POST['agent_agr_start']);
    }

    If ( isset( $_POST['agent_agr_end'] ) ) {
        $dataHead['agent_agr_end'] = formatDateToMysql($_POST['agent_agr_end']);
    }
    if ( !isset($_POST['demurage_day'])){
        $_POST['demurage_day']= 0;
    }
    $QueryClessoroid = mysql_query("SELECT agent_oid FROM $TableAgent WHERE agent_code='".$_POST['agent_code']."'");
    $fetchingOid = mysql_fetch_assoc($QueryClessoroid);

    $dataHead['agent_oid'] = $fetchingOid['agent_oid'];

    $oidAgentAgr = GetParam(PRIMARY_KEY,"");

    $sqlUpdate = "UPDATE $TableName
                 SET
                 agent_oid='$dataHead[agent_oid]',agent_code='$_POST[agent_code]',
                 agent_name='$_POST[agent_name]',agent_agr_start='$dataHead[agent_agr_start]',
                 agent_agr_end='$dataHead[agent_agr_end]',demurage_day='$_POST[demurage_day]',
                 demurage_currency='$_POST[aag_currency_code]',demurage_charge='$_POST[demurage_charge]',
                 detention_day='$_POST[detention_day]',detention_currency='$_POST[aag_currency_code]',detention_charge='$_POST[detention_charge]',
                 agent_agr_mdfon=NOW(),agent_agr_mdfby='".GetStaffName()."'
                 WHERE
                 agent_agr_oid = '$oidAgentAgr'";
    $DBConnection->dbc->query($sqlUpdate);

    $idChgsAll = array_flip(explode("|", $_POST['idchgs']));
    $numCharges = $_POST['numitems2'];
    $detailChargesAdd = array();
    $detailChargesEdit = array();
    $dtChrgs = array();
    $j = $k = 1;

    for($x=1;$x<=$numCharges;$x++){
        if(isset($_POST['charge_oid_'.$x]) && $_POST['charge_oid_'.$x]) {

            if(isset($_POST['agent_agr_charge_oid_'.$x])){
                $detailChargesEdit[$j]['agent_cont_code_code'] = $_POST['container_code_'.$x];
                $detailChargesEdit[$j]['currency_code'] = $_POST['currency_code_'.$x];
                $detailChargesEdit[$j]['charge_amt']  = $_POST['charge_amt_'.$x];
                $detailChargesEdit[$j]['charge_code'] = $_POST['charge_code_'.$x];
                $detailChargesEdit[$j]['charge_oid']  = $_POST['charge_oid_'.$x];
                $detailChargesEdit[$j]['agent_argchg_share_unit']=$_POST['agent_argchg_share_unit_'.$x];
                $detailChargesEdit[$j]['agent_agr_charge_oid']=$_POST['agent_agr_charge_oid_'.$x];

                if(isset($idChgsAll[$_POST['agent_agr_charge_oid_'.$x]])){
                    unset($idChgsAll[$_POST['agent_agr_charge_oid_'.$x]]);
                }
                $j++;
            } else {
                $detailChargesAdd[$k]['agent_cont_code_code'] = $_POST['container_code_'.$x];
                $detailChargesAdd[$k]['currency_code'] = $_POST['currency_code_'.$x];
                $detailChargesAdd[$k]['charge_amt']  = $_POST['charge_amt_'.$x];
                $detailChargesAdd[$k]['charge_code'] = $_POST['charge_code_'.$x];
                $detailChargesAdd[$k]['charge_oid']  = $_POST['charge_oid_'.$x];
                $detailChargesAdd[$k]['agent_argchg_share_unit']=$_POST['agent_argchg_share_unit_'.$x];
                $k++;
            }

        }
    }

    foreach($detailChargesEdit as $key => $val){

        $sqlUpdate = "UPDATE $TableAgentAgrCharges
                      SET
                      charge_oid='$val[charge_oid]',
                      charge_code='$val[charge_code]',
                      agent_agrchg_currency='$val[currency_code]',
                      agent_argchg_share='$val[charge_amt]',
                      agent_argchg_share_unit='$val[agent_argchg_share_unit]',
                      agent_agrchg_mdfon=NOW(),
                      agent_agrchg_mdfon='".GetStaffName()."',
                      agent_cont_code_code='".$val['agent_cont_code_code']."'
                      WHERE
                      agent_agr_charge_oid='$val[agent_agr_charge_oid]'; ";

        $DBConnection->dbc->query($sqlUpdate);
    }

    foreach($detailChargesAdd as $key => $val){

        $sqlInsert = "INSERT INTO $TableAgentAgrCharges
                       (agent_agr_oid, agent_agr_charge_oid, charge_oid, charge_code,
                        agent_agrchg_currency, agent_argchg_share ,agent_argchg_share_unit,
                        agent_agrchg_crton, agent_agrchg_crtby, agent_cont_code_code)
                       VALUES
                       ('$oidAgentAgr',MD5(UUID()),'$val[charge_oid]','$val[charge_code]','$val[currency_code]'
                        ,'$val[charge_amt]','$val[agent_argchg_share_unit]',
                        NOW(),'".GetStaffName()."', '".$val['agent_cont_code_code']."'); ";
        $DBConnection->dbc->query($sqlInsert);

    }

    if($idChgsAll){
        foreach($idChgsAll as $key => $val){
            $sqlDelete = "DELETE FROM $TableAgentAgrCharges WHERE agent_agr_charge_oid='$key';";
            $DBConnection->dbc->query($sqlDelete);
        }
    }

	/** Delete Demurage **/
	$sqlDeleteDem = "DELETE FROM agent_agreement_demdet WHERE agent_agr_oid='$oidAgentAgr';";
	$DBConnection->dbc->query($sqlDeleteDem);
	/** End **/
	
	/** Insert Demurage **/
	$numDem = $_POST['numitems3'];
    $detailDem = array();

    for($x=1;$x<=$numDem;$x++){
        if(isset($_POST['in_out_dem_'.$x])){
            $detailDem[$x]['in_out_dem'] = $_POST['in_out_dem_'.$x];
            $detailDem[$x]['port_oid_dem'] = $_POST['port_oid_dem_'.$x];
            $detailDem[$x]['port_code_dem'] = $_POST['port_code_dem_'.$x];
            $detailDem[$x]['cont_code_code_dem']  = $_POST['cont_code_code_dem_'.$x];
            $detailDem[$x]['period_dem'] = $_POST['period_dem_'.$x];
            $detailDem[$x]['local_currency_dem']  = $_POST['local_currency_dem_'.$x];
            $detailDem[$x]['local_amount_dem']=$_POST['local_amount_dem_'.$x];
            $detailDem[$x]['usd_dem']=$_POST['usd_dem_'.$x];
        }
    }
	foreach($detailDem as $key => $val) {
		$sqlDem = "INSERT INTO agent_agreement_demdet(agent_agr_oid,agent_agr_demdet_oid,in_out,port_oid,port_code,cont_code_code,
														period_oid,local_currency,local_amount,usd_amount,dem_det)
					VALUES ('$oidAgentAgr',MD5(UUID()),'$val[in_out_dem]','$val[port_oid_dem]','$val[port_code_dem]','$val[cont_code_code_dem]',
							'$val[period_dem]','$val[local_currency_dem]','$val[local_amount_dem]','$val[usd_dem]','dem');";
		$DBConnection->dbc->query($sqlDem);
		
	}
	/** End Insert Demurage **/

	/** Insert Detention **/
	$numDet = $_POST['numitems3'];
    $detailDet = array();
    for($x=1;$x<=$numDet;$x++){
        if(isset($_POST['in_out_det_'.$x])){
            $detailDet[$x]['in_out_det'] = $_POST['in_out_det_'.$x];
            $detailDet[$x]['port_oid_det'] = $_POST['port_oid_det_'.$x];
            $detailDet[$x]['port_code_det'] = $_POST['port_code_det_'.$x];
            $detailDet[$x]['cont_code_code_det']  = $_POST['cont_code_code_det_'.$x];
            $detailDet[$x]['period_det'] = $_POST['period_det_'.$x];
            $detailDet[$x]['local_currency_det']  = $_POST['local_currency_det_'.$x];
            $detailDet[$x]['local_amount_det']=$_POST['local_amount_det_'.$x];
            $detailDet[$x]['usd_det']=$_POST['usd_det_'.$x];
        }
    }
	foreach($detailDet as $key => $val) {
		$sqlDem = "INSERT INTO agent_agreement_demdet(agent_agr_oid,agent_agr_demdet_oid,in_out,port_oid,port_code,cont_code_code,
														period_oid,local_currency,local_amount,usd_amount,dem_det)
					VALUES ('$oidAgentAgr',MD5(UUID()),'$val[in_out_det]','$val[port_oid_det]','$val[port_code_det]','$val[cont_code_code_det]',
							'$val[period_det]','$val[local_currency_det]','$val[local_amount_det]','$val[usd_det]','det');";
		$DBConnection->dbc->query($sqlDem);
		
	}
	/** End Insert Detention **/
    header("Location: $FileName");
    exit;
}

function FormDelete() {
    global $DBCoins,$DBGeneral,$DBConnection,$FileName,$TableName;
    global $TableLessorContainer,$TableAgentAgrCharges;

    $DBConnection->Delete("$TableName",PRIMARY_KEY);
    $DBConnection->Delete("$TableAgentAgrCharges",PRIMARY_KEY);
    header("Location: $FileName");
    exit;
}

function FormCancel () {
    global $DBCoins,$DBGeneral,$FileName;
    header("Location: ".$FileName);
    exit;
}
//*************** USER FUNCTION *******************
function buildsqlfield ($table, $arrayfield) {
        global $DBCoins,$DBGeneral,$DBConnection;
        for ($x=0;$x<count($arrayfield);$x++) {
            $DBConnection->add_htmlfield($table,$arrayfield[$x],GetParam($arrayfield[$x],""));
        }
}

function formatDateToMysql($date){
     $YYYY  = substr($date,6,4);
     $MM    = substr($date,3,2);
     $DD    = substr($date,0,2);
     $dateMysql = "$YYYY-$MM-$DD";
     return $dateMysql;
}
function Create_agr_code($agent_code){
global $DBCoins,$DBGeneral,$DBConnection,$FileName,$TableCount;
    $year=date('y');
    $mount=date('m');
    $prefix="AA$agent_code$year$mount";

    $req_no_sql="SELECT count FROM $TableCount WHERE count_id='".$prefix."'";

    $req_no_exec=mysql_query($req_no_sql);
    $req_no_num = mysql_num_rows($req_no_exec);
    if($req_no_num > 0){

        $req_no_def=mysql_fetch_array($req_no_exec);

        $agent_agr_count=$req_no_def[0] + 1;

    }//end if
    else {
        $query_add ="Insert INTO $TableCount(count_id , count ) VALUES ('".$prefix."','0') ";
        $DBConnection->dbc->query($query_add);
        $agent_agr_count= 1;
    }
    $digitnol = strlen($agent_agr_count);
    switch ($digitnol) {
        case 1: $nol = '00000'; break;
        case 2: $nol = '0000'; break;
        case 3: $nol = '000'; break;
        case 4: $nol = '00'; break;
        case 5: $nol = '0'; break;
        case 6: $nol = ''; break;
    }
    $agent_agr_sequence=$nol.$agent_agr_count;

    $agent_agr_code=$prefix.$agent_agr_sequence;

    $SQL_Count="UPDATE $TableCount set count='$agent_agr_count' WHERE count_id='".$prefix."' ";
    $DBConnection->dbc->query($SQL_Count);

    return $agent_agr_code;
}
?>
